package com.zz.study.sharding.util;

import java.sql.*;

public class SqlUtil {

    //定义mysql加载驱动，老版本的mysqljar包用的驱动参数时“com.mysql.jdbc.Driver”，新版的如下所示
    private static final String driver = "com.mysql.cj.jdbc.Driver";
    //数据库的ip地址、端口号、库名
    //定义连接
    private static Connection conn;
    //定义sql执行状态
    private static PreparedStatement prepare;
    //定义结果字段
    private static ResultSet result;

    //获取预约单号方法
    public static void creataStarRocksTable(String jdbcUrl,String userName,String password,
                                              String tableName, String startTimeStr, String endTimeStr) throws SQLException, Exception {
        //加载驱动
        Class.forName(driver).newInstance();
        System.out.println("驱动加载成功");
        //连接数据库
        conn = DriverManager.getConnection(jdbcUrl, userName, password);
        System.out.println("数据库连接成功");


        String SQL =
                "CREATE TABLE IF NOT EXISTS `" + tableName + "` (" +
                        "  `id` bigint(20) NOT NULL COMMENT ''," +
                        "  `RadarType` tinyint(4) NULL COMMENT '雷达类型 0:没有雷达源 非雷达目标 1:雷达目标'," +
                        "  `Speed` float NULL COMMENT '航速', " +
                        "  `RecordTime` datetime NULL COMMENT '数据最后刷新时间'," +
                        "  `VesselType` tinyint(4) NULL COMMENT '船只种类'," +
                        "  `BeidouType` tinyint(4) NULL COMMENT '北斗类型 0:没有北斗源 非北斗目标 1:北斗目标'," +
                        "  `Heading` float NULL COMMENT '艏向'," +
                        "  `AISType` tinyint(4) NULL COMMENT 'AIS目标类型 0:没有AIS源 非AIS目标 1:A级 2:B级 3:辅助导航'," +
                        "  `SourceNames` varchar(1500) NULL COMMENT '源名称集合 用逗号分开'," +
                        "  `Lon` double NULL COMMENT '经度'," +
                        "  `AISName` varchar(150) NULL COMMENT 'AIS目标名称'," +
                        "  `TarID` int(11) NULL COMMENT '目标ID 非0'," +
                        "  `Uid` varchar(300) NULL COMMENT '目标唯一ID'," +
                        "  `MMSI` int(11) NULL COMMENT 'MMSI号 0表示没有MMSI号'," +
                        "  `Length` int(11) NULL COMMENT '船长'," +
                        "  `State` tinyint(4) NULL COMMENT ''," +
                        "  `ArchiveTime` datetime NULL COMMENT ''," +
                        "  `TerminalID` varchar(250) NULL COMMENT ''," +
                        "  `Course` float NULL COMMENT ''," +
                        "  `Lat` double NULL COMMENT ''" +
                        ") ENGINE=OLAP \n" +
                        "DUPLICATE KEY(`id`) \n" +
                        "COMMENT \"轨迹历史表\" \n" +
                        "PARTITION BY RANGE(`RecordTime`)(START ('" + startTimeStr + "') END('" + endTimeStr + "') EVERY (INTERVAL 1 DAY)) \n" +
                        "DISTRIBUTED BY HASH(`id`) BUCKETS 8 \n" +
                        "PROPERTIES (" +
                        "\"replication_num\" = \"1\",\n" +
                        "\"in_memory\" = \"false\",\n" +
                        "\"storage_format\" = \"DEFAULT\",\n" +
                        "\"enable_persistent_index\" = \"false\",\n" +
                        "\"replicated_storage\" = \"true\",\n" +
                        "\"compression\" = \"LZ4\"\n" +
                        ");";

        try {
        //执行sql
        prepare = conn.prepareStatement(SQL);
        //难到返回值
         prepare.execute(SQL);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {

            try {
                if (result != null) result.close();
                if (prepare != null) prepare.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}
